Alterations

Alterations#

MySQL allows us to change our mind about the entities we create and alter them. We can rename tables, add, remove, or rename columns, change type of an existing column, etc.

Example Syntax#

ALTER TABLE table

CHANGE oldColumnName newColumnName <datatype> <restrictions>;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/17lesson.sh and wait for the MySQL prompt to start-up.

1. Let’s say we want to rename the column FirstName to First_Name for the Actors table. We can do so as follows:

   ALTER TABLE Actors CHANGE FirstName First_Name varchar(120);
   

We not only change the column name, but we also change the column length from 20 to 120. If we only wanted to rename the column, we would still need to re-specify the type of the column as well as any other clauses that were specified the first time.

2. We can use the MODIFY keyword if we wish to alter the type or the clauses for a column. For instance, we can specify the default value for the column First_Name to be the string “Anonymous” as follows:

   ALTER TABLE Actors MODIFY First_Name varchar(20) DEFAULT "Anonymous";
   

We can also use the CHANGE statement but that will require us to specify the same column name twice as we aren’t renaming the column.

ALTER TABLE Actors CHANGE First_Name First_Name varchar(20) DEFAULT "Anonymous";

3. We have to be cautious when trying to change the type of an existing column. For instance, if we try to change the first name column from type varchar to int, we’ll run into an error (as shown below) because the conversion is nonsensical.

   ALTER TABLE Actors MODIFY First_Name INT;
   

On the contrary, we can easily convert the type of a column that doesn’t result in data loss. For instance, we can change the column first name to have a varchar length of 300 as shown below:

ALTER TABLE Actors MODIFY First_Name varchar(300);

4. We can also add a column to an existing table. We can add a new column MiddleName to the Actors table using the following query:

   ALTER TABLE Actors ADD MiddleName 
   varchar(100);
   

5. We can also remove the newly added column using the DROP statement as follows:

   ALTER TABLE Actors DROP MiddleName;
   

6. We can also control the position of the new column within the table using the FIRST or AFTER keyword. The following statement adds the middle name as the first column:

   ALTER TABLE Actors ADD MiddleName varchar(100) FIRST;
   

7. Now we’ll drop the middle name column and add it after the date of birth (DoB) column as follows:

 ALTER TABLE Actors DROP MiddleName;
 ALTER TABLE Actors ADD MiddleName varchar(100) AFTER DoB;

If an index is defined on a column, dropping the column also removes the index, if the index consists of only that one column.

8. We can combine several alterations in a single MySQL statement separated by comma. In fact, combining alterations is much more efficient as it avoids the cost of creating a new table, copying data from the old table to the new, dropping the old table, and renaming the old table to the new table for each alteration. In the example below, we drop the middle name column and recreate it using a slightly different column name, all in a single statement.

   ALTER TABLE Actors DROP MiddleName, ADD Middle_Name varchar(100);
   

9. For some alter operations under the hood, MySQL creates a new table with the requested alter changes, copies the data from the old table to the new one, deletes the old table, and then renames the new table to Actors. An alter operation can be expensive if the table needs to be rebuilt.